﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace MsSpatialTools
{
    public class DBHelper
    {
        #region 链接字符串
        public static string ConnectionString;
        public static string SqlServer;
        public static string Database;
        public static string UserID;
        public static string Password;
        public static bool Trusted_Connection;
        public static string xlsPath;
        public static string getConnectionString
        {
            get
            {
                return getSqlConnection.ConnectionString;
            }
        }
        private static SqlConnection getSqlConnection
        {
            get
            {
                SqlConnection cn = new SqlConnection();
                string str_cn;
                if (Trusted_Connection)
                {
                    str_cn = @"Server={0};Database={1};Trusted_Connection=true;Connect Timeout=30;";
                    str_cn = string.Format(str_cn, SqlServer, Database);
                }
                else
                {
                    str_cn = @"Server={0};Database={1};";
                    str_cn = string.Format(str_cn, SqlServer, Database);
                    str_cn += "User ID={0};Password={1};Connect Timeout=30;";
                    str_cn = string.Format(str_cn, UserID, Password);
                }
                //cn.ConnectionTimeout = 30;
                cn.ConnectionString = str_cn;

                //cn.ConnectionString = "server=(local);Integrated Security=true;Initial Catalog=master;";
                return cn;
                //SELECT [name], [filename] FROM dbo.sysdatabases
            }
            
        }
        private static string getSqlConnectionString
        {
            get
            {
                //SqlConnection cn = new SqlConnection();
                string str_cn;
                if (Trusted_Connection)
                {
                    str_cn = @"Server={0};Database={1};Trusted_Connection=true;Connect Timeout=30;";
                    str_cn = string.Format(str_cn, SqlServer, Database);
                }
                else
                {
                    str_cn = @"Server={0};Database={1};";
                    str_cn = string.Format(str_cn, SqlServer, Database);
                    str_cn += "User ID={0};Password={1};Connect Timeout=30;";
                    str_cn = string.Format(str_cn, UserID, Password);
                }
                //cn.ConnectionTimeout = 30;
                //cn.ConnectionString = str_cn;

                //cn.ConnectionString = "server=(local);Integrated Security=true;Initial Catalog=master;";
                return str_cn;
                //SELECT [name], [filename] FROM dbo.sysdatabases
            }

        }
        private static OleDbConnection getOleDbConnection
        {
            get
            {
                string s_ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = {0};Extended Properties = Excel 8.0";
                s_ConnString = string.Format(s_ConnString, xlsPath);
                OleDbConnection cn = new OleDbConnection(s_ConnString);
                return cn;
            }
        }
        #endregion
        #region SQL Server相关操作
        public static bool testConnection()
        {
            //SqlConnection cn = getSqlConnection;
            SqlConnection cn = new SqlConnection(getConnectionString);
            try
            {
                cn.Open();
                cn.Close();
                cn.Dispose();
                return true;
            }
            catch(Exception e1)
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
                return false;
            }

        }
        /// <summary>
        /// 获取数据库数据
        /// </summary>
        /// <returns></returns>
        public static DataTable getDataTable(string mysql)
        {
            //SqlConnection cn = getSqlConnection;
            SqlConnection cn = new SqlConnection(getConnectionString);
            SqlCommand cmd = cmd = new SqlCommand(mysql, cn);
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                cn.Close();
                cn.Dispose();
            }
            catch (Exception e1) { }
            finally
            {
                cmd.Dispose();
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return dt;
        }
        /// <summary>
        /// 获取所有数据库名
        /// </summary>
        /// <returns></returns>
        public static DataTable getSQLSchemaDB()
        {
            string sql = "SELECT [name], [filename] FROM dbo.sysdatabases";
            //SqlConnection cn = getSqlConnection;
            SqlConnection cn = new SqlConnection(getConnectionString);
            SqlCommand cmd = new SqlCommand(sql, cn);
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception e1){ }
            finally
            {
                cmd.Dispose();
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return dt;
        }
        /// <summary>
        /// 获取数据库的所有表名
        /// </summary>
        /// <returns></returns>
        public static DataTable getSQLSchemaTable(string dbName)
        {
            Database = dbName;
            //SqlConnection cn = getSqlConnection;
            SqlConnection cn = new SqlConnection(getConnectionString);
            DataTable dt = new DataTable();
            string sql = " select * from sysobjects where type = 'U' and uid=1";
            SqlCommand cmd = new SqlCommand(sql, cn);
            try
            {
                cn.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception e1){ }
            finally
            {
                cmd.Dispose();
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return dt;
        }

        
        /// <summary>
        /// 获取表字段名
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public static DataTable getSQLColumnName(string tbName)
        {
            //Database = dbName;
            //SqlConnection cn = getSqlConnection;
            SqlConnection cn = new SqlConnection(getConnectionString);
            DataTable dt = new DataTable();
            string sql = " select Name from SysColumns where id=Object_Id('{0}')";
            SqlCommand cmd = new SqlCommand(sql, cn);
            try
            {
                cn.Open();       
                sql = string.Format(sql, tbName);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception e1){ }
            finally
            {
                cmd.Dispose();
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return dt;
        }
        /// <summary>
        /// 获取 图形 字段名
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public static string getSQLGeoName(string tbName)
        {
            //Database = dbName;

            //SqlConnection cn = getSqlConnection;
            SqlConnection cn = new SqlConnection(getConnectionString);
            DataTable dt;
            string sql = "select F_GEOMETRY_COLUMN from ST.GEOMETRY_COLUMNS WHERE F_TABLE_NAME='" + tbName + "'";
            SqlCommand cmd = new SqlCommand(sql, cn);
            try
            {
                cn.Open();
                //string strSQL = "select F_GEOMETRY_COLUMN from ST.GEOMETRY_COLUMNS WHERE F_TABLE_NAME='" + this.Table + "'";
                dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);

                cn.Close();
                if (dt.Rows.Count > 0)
                {
                    string s = dt.Rows[0][0].ToString();
                    return s;
                }
            }
            catch (Exception e1){ }
            finally
            {
                cmd.Dispose();
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return "";

            
        }
        /// <summary>
        /// 获取DataSet
        /// </summary>
        /// <returns></returns>
        public static DataSet getDataSet(string mysql)
        {
            //SqlConnection cn = getSqlConnection;
            SqlConnection cn = new SqlConnection(getConnectionString);
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(mysql, cn);
            try
            {
                cn.Open();
                
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
            }
            catch (Exception e1){ }
            finally
            {
                cmd.Dispose();
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return ds;
        }
        #endregion
        #region Excel相关操作
        public static DataTable getExcelSchemaTable()
        {       
            OleDbConnection cn = getOleDbConnection;
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            }
            catch (Exception e1){ }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return dt;
        }
        public static DataTable getExcelSchemaColumn(string TableName)
        {
            OleDbConnection cn = getOleDbConnection;
            DataTable dt=new DataTable ();
            try
            {
                cn.Open();
                dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
                                    new object[] { null, null, TableName, null });
            }
            catch (Exception e1){ }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return dt;
        }
        public static DataTable getExcelDate(string TableName)
        {
            OleDbConnection cn = getOleDbConnection;
            string sql = string.Format("select * from [{0}]", TableName);
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception e1){ }
            finally
            { 
                cmd.Dispose();
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return dt;
        }
        #endregion

        #region 根据Databale建立数据库表
        public static void buildTB(DataTable dt)
        {
             
            
        }
        #endregion
    }
}
